package service.db;

import java.sql.*;
import java.util.HashMap;
import java.util.logging.*;
import common.CustomException;

/**
 * Database class for MySQL used to get generic function names
 * incase of a DBMS switch just create a file with the same method names for that DBMS
 * 
 * @author Fredrik Kempe
 */
public class MySQL {

    private static Connection connection = null;
    private static PreparedStatement statement = null;  //PreparedStatment is used insted of Statment for sql-injection protection

    /**
     * open connection to database server
     */
    public static void openConnection() {

        try {

            Class.forName("com.mysql.jdbc.Driver");
            String connectionUrl = "jdbc:mysql://localhost/imageshare";
            connection = DriverManager.getConnection(connectionUrl, "root", "datateknik321");
        }
        catch (ClassNotFoundException ex) {

            System.out.println("Error: Couldn't find MySQL database driver");
            System.exit(1);
        }
        catch (SQLException ex) {

            System.out.println("Error: Couldn't connect to MySQL database-server");
            System.exit(1);
        }
    }


    /**
     * get number of results based on a resultset
     * 
     * @param rs - ResultSet
     * @return number of results - int
     */
    public static int numberOfResults(ResultSet rs) {
        try {
            rs.last();
            int numberOfRows = rs.getRow();
            rs.first();

            return numberOfRows;
        } catch (SQLException ex) {
            Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }


    /**
     * send querys to the database and return records
     * 
     * @param sql - String
     * @return results - Resultset
     */
    public static ResultSet query(String sql) {
        try {
            statement = connection.prepareStatement(sql);
            return statement.executeQuery(sql);
        }
        catch (SQLException ex) {

            System.out.println("Error: Something went wrong while trying to execute sql statement "+sql);
            System.exit(1);
        }

        return null;
    }


    /**
     * fetch multiple records
     * 
     * @param sql - String
     * @return result - ResultSet
     */
    public static ResultSet fetchAll(String sql) {
        ResultSet result = query(sql);

        int numberOfResult = numberOfResults(result);
        if(numberOfResult == 0) {
            return null;
        }
        else {
            return result;
        }
    }

    /**
     * fetch singel record
     * Converts a resultset to hashmap
     *
     * @param sql - String
     * @return result - HashMap
     */
    public static HashMap<String, String> fetchSingel(String sql) {
        ResultSet result = query(sql);

        int numberOfResult = numberOfResults(result);
        if(numberOfResult == 0) {
            return null;
        }
        else {
            try {
                result.first();
                HashMap<String, String> resultArray = new HashMap<String, String>();

                int size = result.getMetaData().getColumnCount();
                for(int i=1; i<=size; i++) {
                    String key = result.getMetaData().getColumnLabel(i);
                    String value = result.getString(key);
                    resultArray.put(key, value);
                }

                return resultArray;
            } catch (SQLException ex) {
                Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
    }


    /**
     * update/add a record
     * @param sql - String
     */
    public static void update(String sql) throws CustomException {
        try {
            statement = connection.prepareStatement(sql);
            statement.executeUpdate(sql);
        }
        catch (SQLException ex) {
            System.out.println("Error: Something went wrong while trying to execute sql statement "+sql);
            throw new CustomException(ex.getMessage());
        }
    }
}
